package DBLayer;
import ModelLayer.Discount;
import java.sql.*;
import java.util.ArrayList;

public class DBDiscount {
    // <editor-fold defaultstate="collapsed" desc="Attributes & Constructors">
    private Connection conn;

    public DBDiscount() {
        conn = SQLConnector.getInstance().getConnection();
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL SELECT Methods">
    public Discount getDiscountByID(int id) {
        return selectSingle("SELECT `id_discount`, `id_discount_type`, `id_customer`, `name`, `value`, `quantity`, `quantity_per_user`, `cumulable`, `cumulable_reduction`, UNIX_TIMESTAMP(`date_from`) AS `date_from`, UNIX_TIMESTAMP(`date_to`) AS `date_to`, `minimal`, `active`, `description` FROM `ps_discount` LEFT JOIN `ps_discount_lang` USING (`id_discount`) WHERE (`id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL) AND `id_discount` = " + id + " LIMIT 0, 1;");
    }

    public ArrayList<Discount> getDiscountList() {
        return selectMultiple("SELECT `id_discount`, `id_discount_type`, `id_customer`, `name`, `value`, `quantity`, `quantity_per_user`, `cumulable`, `cumulable_reduction`, UNIX_TIMESTAMP(`date_from`) AS `date_from`, UNIX_TIMESTAMP(`date_to`) AS `date_to`, `minimal`, `active`, `description` FROM `ps_discount` LEFT JOIN `ps_discount_lang` USING (`id_discount`) WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " OR `id_lang` IS NULL ORDER BY `id_discount` ASC;");
    }

    public ArrayList<Integer> getDiscountIDsByCategoryID(int id) {
        return selectIDs("SELECT `id_discount` FROM `ps_discount_category` WHERE `id_category` = " + id + " ORDER BY `id_discount` ASC;");
    }

    public ArrayList<Integer> getDiscountIDsByOrderID(int id) {
        return selectIDs("SELECT `id_discount` FROM `ps_order_discount` WHERE `id_order` = " + id + " ORDER BY `id_discount` ASC;");
    }

    private Discount selectSingle(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        Discount discount = new Discount();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            if (result.next()) {
                discount = buildDiscount(result);
            }
            System.out.println("Fetching Discount Object");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return discount;
    }

    private ArrayList<Discount> selectMultiple(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Discount> discounts = new ArrayList<Discount>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                Discount discount = new Discount();
                discount = buildDiscount(result);
                discounts.add(discount);
            }
            System.out.println("Fetching Discount List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return discounts;
    }

   private ArrayList<Integer> selectIDs(String sqlStatement) {
        Statement stmt = null;
        ResultSet result = null;
        ArrayList<Integer> ids = new ArrayList<Integer>();

        try {
            stmt = conn.createStatement();
            result = stmt.executeQuery(sqlStatement);

            while (result.next()) {
                ids.add(result.getInt("id_discount"));
            }
            System.out.println("Fetching Discount ID List");
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return ids;
    }

   private Discount buildDiscount(ResultSet result) {
        DBCategory dbCategory = new DBCategory();
        DBOrder dbOrder = new DBOrder();
        Discount discount = new Discount();
        try {
            discount.setIDDiscount(result.getInt("id_discount"));
            discount.setIDDiscountType(result.getInt("id_discount_type"));
            discount.setIDCustomer(result.getInt("id_customer"));
            discount.setName(result.getString("name"));
            discount.setValue(result.getFloat("value"));
            discount.setQuantity(result.getInt("quantity"));
            discount.setQuantityPerUser(result.getInt("quantity_per_user"));
            if (result.getInt("cumulable") == 1) {
                discount.setCumulable(true);
            } else {
                discount.setCumulable(false);
            }
            if (result.getInt("cumulable_reduction") == 1) {
                discount.setCumulableReduction(true);
            } else {
                discount.setCumulableReduction(false);
            }
            discount.setDateFrom(result.getInt("date_from"));
            discount.setDateTo(result.getInt("date_to"));
            discount.setMinimal(result.getFloat("minimal"));
            if (result.getInt("active") == 1) {
                discount.setActive(true);
            } else {
                discount.setActive(false);
            }
            discount.setDescription(result.getString("description"));
            discount.setCategoryIDs(dbCategory.getCategoryIDsByDiscountID(result.getInt("id_discount")));
            discount.setOrderIDs(dbOrder.getOrderIDsByDiscountID(result.getInt("id_discount")));

        } catch (Exception e) {
            System.out.println("Exception: " + e.getMessage());
        }
        return discount;
    }
    // </editor-fold>

    // <editor-fold defaultstate="collapsed" desc="SQL INSERT, UPDATE & DELETE Methods">
    public int addDiscount(Discount discount) {
        int returnID = 0;
        Statement stmt = null;
        ResultSet result = null;

        String query = "INSERT INTO `ps_discount_lang` (`id_discount`, `id_lang`, `description`) "
                + "VALUES (" + discount.getIDDiscount() + ", " + Constants.DEFAULT_LANGUAGE + ", '" + discount.getDescription() + "');";

        query += "INSERT INTO `ps_discount` (`id_discount`, `id_discount_type`, `id_customer`, `name`, `value`, `quantity`, `quantity_per_user`, `date_from`, `date_to`, `minimal`, `cumulable`, `cumulable_reduction`, `active`) "
                + "VALUES (" + discount.getIDDiscount() + ", " + discount.getIDDiscountType() + ", " + discount.getIDCustomer() + ", '" + discount.getName() + "', " + discount.getValue() + ", " + discount.getQuantity() + ", " + discount.getQuantityPerUser() + ", FROM_UNIXTIME(" + discount.getDateFrom() + "), FROM_UNIXTIME(" + discount.getDateTo() + "), " + discount.getMinimal() + ", ";
        if (discount.getCumulable() == true)            { query += "1, "; }
        else                                            { query += "0, "; }
        if (discount.getCumulableReduction() == true)   { query += "1, "; }
        else                                            { query += "0, "; }
        if (discount.getActive() == true)               { query += "1);"; }
        else                                            { query += "0);"; }
        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
            result = stmt.getGeneratedKeys();
            if (result.next()) {
                returnID = result.getInt(1);
            }
            System.out.println("Added Discount ID " + returnID);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (result != null) {
                try {
                    result.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                result = null;
            }
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public int updateDiscount(Discount discount, int id) {
        int returnID = 0;
        Statement stmt = null;
	String query = "UPDATE `ps_discount` SET "
                + "`id_discount`        = "  + discount.getIDDiscount() + ", "
                + "`id_discount_type`   = "  + discount.getIDDiscountType() + ", "
                + "`id_customer`        = "  + discount.getIDCustomer() + ", "
                + "`name`               = '" + discount.getName() + "', "
                + "`value`              = "  + discount.getValue() + ", "
                + "`quantity`           = "  + discount.getQuantity() + ", "
                + "`quantity_per_user`  = "  + discount.getQuantityPerUser() + ", ";
        if (discount.getCumulable() == true)            { query += "`cumulable` = 1, "; }
        else                                            { query += "`cumulable` = 0, "; }
        if (discount.getCumulableReduction() == true)   { query += "`cumulable_reduction` = 1, "; }
        else                                            { query += "`cumulable_reduction` = 0, "; }
        query +=  "`date_from`          = FROM_UNIXTIME("  + discount.getDateFrom() + "), "
                + "`date_to`            = FROM_UNIXTIME("  + discount.getDateTo() + "), "
                + "`minimal`            = "  + discount.getMinimal() + ", ";
        if (discount.getActive() == true)               { query += "`active` = 1, "; }
        else                                            { query += "`active` = 0, "; }
        query += "WHERE `id_discount` = " + id + " LIMIT 1; ";

        query += "UPDATE `ps_discount_lang` SET "
                + "`id_discount`        = "  + discount.getIDDiscount() + ", "
                + "`description`        = "  + discount.getDescription() + " "
                + "WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " AND `id_discount` = " + id + " LIMIT 1;";

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate(query);
            returnID = discount.getIDDiscount();
            System.out.println("Updated Discount ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
        return returnID;
    }

    public void deleteDiscount(int id) {
        Statement stmt = null;

        try {
            stmt = conn.createStatement();
            stmt.executeUpdate("DELETE FROM `ps_discount` WHERE `id_discount` = " + id + " LIMIT 1; DELETE FROM `ps_discount_lang` WHERE `id_lang` = " + Constants.DEFAULT_LANGUAGE + " AND `id_discount` = " + id + " LIMIT 1;");
            System.out.println("Deleted Discount ID " + id);
        } catch (SQLException e) {
            System.out.println("SQLException: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("VendorError: " + e.getErrorCode());
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.out.println("SQLException: " + e.getMessage());
                    System.out.println("SQLState: " + e.getSQLState());
                    System.out.println("VendorError: " + e.getErrorCode());
                }
                stmt = null;
            }
        }
    }
    // </editor-fold>
}
